package org.zhuzx.util;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 基于POI的Excel工具类
 * @author	zhuzx	2017年6月5日
 */
public class ExcelUtil {
	
	public static byte[] createExcel(boolean needXlsx, String[] sheetNames, boolean useOriginalLabel, String... sqls) {
		@SuppressWarnings("resource")
		Workbook wb = needXlsx ? new XSSFWorkbook() : new HSSFWorkbook();
		if (sheetNames == null || sheetNames.length != sqls.length) {
			sheetNames = new String[sqls.length];
			for (int i = 0; i < sqls.length; i++) {
				sheetNames[i] = "Sheet" + i;
			}
		}
		for (int i = 0; i < sqls.length; i++) {
			List<String> types = new ArrayList<>();
			List<Map<String, String>> dataList;
			try {
				dataList = executeQuery(sqls[i], types);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
			Sheet sheet = wb.createSheet(sheetNames[i]);
			Collection<String> headers;
			if (useOriginalLabel) {
				headers = dataList.get(0).keySet();
			} else {
				String simpleSql = sqls[i].trim().replaceAll("\\s+", " ").toLowerCase();
				//若这条sql是要查询某个表的全部字段，则取from关键字后面的第一个表的字段注释作为列头。
				String[][] columns =
//						simpleSql.startsWith("select * from ") ?
						JdbcUtil.getMetaData(MicroUtil.extractFirstTableName(simpleSql))
//						: SqlTextParser.getColumns(simpleSql)
						;
				List<String> cnHeaders = Arrays.asList(columns[2]);
				for (int j = 0; j < cnHeaders.size(); j++) {
					if (GeneralUtil.isBlank(cnHeaders.get(j))) {//如果没有中文注释就取字段名为列头
						cnHeaders.set(j, columns[0][j]);
					}
				}
//				if (cnHeaders.size() < headers.size()) {//select子句中不是只有第一个表的字段，后面还有其它表的字段。
//					cnHeaders = new ArrayList<>(cnHeaders);
//					//TODO cnHeaders.add("申请单id");
//				}
				headers = cnHeaders;
			}
			boolean[] isNumValue = new boolean[headers.size()];
			for (int j=0; j<types.size(); j++) {
				try {
					isNumValue[j] = Number.class.isAssignableFrom(Class.forName(types.get(j)));
				} catch (ClassNotFoundException e) {
					throw new RuntimeException(e);
				}
			}
			addRow(sheet, headers, new boolean[headers.size()], 0);
			for (Map<String, String> map : dataList) {
				addRow(sheet, map.values(), isNumValue);
			}
		}
		return getBytes(wb);
	}
	
	/**
	 * isNumValueArray是临时方案，紧急待办：支持List<Map<String,Object>>并识别值类型自动选择cell格式。
	 */
	@SafeVarargs
	public static byte[] createExcel(boolean needXlsx, String[] sheetNames, boolean[][] isNumValueArray,
			List<Map<String,String>>... lists) {
		Workbook wb = needXlsx ? new XSSFWorkbook() : new HSSFWorkbook();
		if (sheetNames == null || sheetNames.length != lists.length) {
			sheetNames = new String[lists.length];
			for (int i = 0; i < lists.length; i++) {
				sheetNames[i] = "Sheet" + i;
			}
		}
		for (int i = 0; i < lists.length; i++) {
			List<Map<String,String>> dataList = lists[i];
			Sheet sheet = wb.createSheet(sheetNames[i]);
//			if (GeneralUtil.isEmpty(dataList)) {
//				continue;
//			}
			Set<String> headers = dataList.get(0).keySet();
			boolean[] allFlase = new boolean[headers.size()];
			boolean[] isNumValue;
			if (isNumValueArray == null || isNumValueArray[i] == null) {
				isNumValue = allFlase;
			} else {
				isNumValue = isNumValueArray[i];
			}
			addRow(sheet, headers, allFlase, 0);
			for (Map<String, String> map : dataList) {
				addRow(sheet, map.values(), isNumValue);
			}
		}
		return getBytes(wb);
	}
	
	private static void addRow(Sheet sheet, Collection<?> values, boolean[] isNumValue, int rowNum) {
		Row row = sheet.createRow(rowNum);
		int columnNum = 0;
		for (Object value : values) {
			CellType type = isNumValue[columnNum] ? CellType.NUMERIC : CellType.STRING;
			Cell cell = row.createCell(columnNum, type);
			String strValue = Objects.toString(value, "").trim();
			if (strValue.length() > 0) {
				if (isNumValue[columnNum]) {
					cell.setCellValue(Double.parseDouble(strValue));
				} else {
					if (strValue.length() > 100) {
						strValue = strValue.substring(0, 100) + "...";
					}
					cell.setCellValue(strValue);
				}
			}
			columnNum++;
		}
	}
	
	private static void addRow(Sheet sheet, Collection<?> values, boolean[] isNumValue) {
		addRow(sheet, values, isNumValue, sheet.getLastRowNum()+1);
	}
	
	private static List<Map<String,String>> executeQuery(String sql, List<String> carryTypeReturn) throws SQLException {
		Connection conn = JdbcUtil.getDbConn(null);
		Statement stm = conn.createStatement();
		ResultSet rs = stm.executeQuery(sql);
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		String[] columnName = new String[columnCount];
		String[] columnType = new String[columnCount];
		for (int i=0; i<columnCount; i++) {
			columnName[i] = rsmd.getColumnLabel(i+1);
			columnType[i] = rsmd.getColumnClassName(i+1);
		}
		List<Map<String,String>> list = new ArrayList<>();
		while (rs.next()) {
			Map<String,String> row = new LinkedHashMap<>();
			for (int i=0; i<columnCount; i++) {
				String value = rs.getString(columnName[i]);
				if (!GeneralUtil.isBlank(value) && columnType[i].equals("java.sql.Timestamp")) {
					value = value.substring(0, 19);
				}
				row.put(columnName[i], value);
			}
			list.add(row);
		}
		carryTypeReturn.addAll(Arrays.asList(columnType));
		stm.close();
		conn.close();
		return list;
	}
	
	/**
	 * 约定每个data二维数组的第一行为列头（String[]），第二行为类型（boolean[]）。
	 */
	@SafeVarargs
	public static byte[] createExcel(boolean needXlsx, String[] sheetNames, List<List<String>> headersList,
			boolean[][] isNumValueArray, List<List<Object>>... lists) {
		Workbook wb = needXlsx ? new XSSFWorkbook() : new HSSFWorkbook();
		if (sheetNames == null || sheetNames.length != lists.length) {
			sheetNames = new String[lists.length];
			for (int i = 0; i < lists.length; i++) {
				sheetNames[i] = "Sheet" + i;
			}
		}
		if (headersList.size() != lists.length) {
			try {
				wb.close();
			} catch (IOException e) {
				throw new RuntimeException(e);
			}
			throw new RuntimeException("各个sheet的列头列表必须与数据集列表一一对应");
		}
		for (int i = 0; i < lists.length; i++) {
			List<List<Object>> dataList = lists[i];
			Sheet sheet = wb.createSheet(sheetNames[i]);
//			if (GeneralUtil.isEmpty(dataList)) {
//				continue;
//			}
			List<String> headers = headersList.get(i);
			boolean[] allFlase = new boolean[headers.size()];
			boolean[] isNumValue;
			if (isNumValueArray == null || isNumValueArray[i] == null) {
				isNumValue = allFlase;
			} else {
				isNumValue = isNumValueArray[i];
			}
			addRow(sheet, headers, allFlase, 0);
			for (List<?> list : dataList) {
				addRow(sheet, list, isNumValue);
			}
		}
		return getBytes(wb);
	}
	
	/**
	 * 获取一个Excel工作簿的全部字节，并关闭wb相关的流或文件（不能再对wb对象做任何操作）。
	 */
	public static byte[] getBytes(Workbook wb) {
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		try {
			wb.write(os);
			wb.close();
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
		return os.toByteArray();
	}
	
	public static List<Map<String,String>> createListMapSSFromExcel(InputStream fileIn,
			boolean isXlsx, int sheetIndex) {
		try {
			Workbook wb = isXlsx ? new XSSFWorkbook(fileIn) : new HSSFWorkbook(fileIn);
			List<Map<String,String>> list = createListMapSSFromExcelSheet(wb.getSheetAt(sheetIndex));
			wb.close();
			fileIn.close();
			return list;
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
	}
	
	//@Deprecated//CellType公式单元格如何处理？提供选项提取文本还是得到0？
	public static List<Map<String,String>> createListMapSSFromExcelSheet(Sheet sheet) {
		List<Map<String,String>> list = new ArrayList<>(sheet.getLastRowNum() + 1);
		Row head = sheet.getRow(0);
		if (head == null)
			return list;
		String[] titles = new String[head.getLastCellNum()];
		for (int i=0; i<head.getLastCellNum(); i++) {
			Cell cell = head.getCell(i);
//			cell.setCellType(Cell.CELL_TYPE_STRING);
//			titles[i] = cell.getStringCellValue();
			titles[i] = getStringCellValue(cell);
		}
		Workbook wb = sheet.getWorkbook();
		FormulaEvaluator evaluator = (wb instanceof HSSFWorkbook) ?
				new HSSFFormulaEvaluator((HSSFWorkbook) wb) : new XSSFFormulaEvaluator((XSSFWorkbook) wb);
		evaluator.evaluateAll();
		for (int i=1; i<=sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			if (row == null)
				continue;
			Map<String,String> map = new LinkedHashMap<>();
			for (int j=0; j<row.getLastCellNum(); j++) {
				Cell cell = row.getCell(j);
				if (cell == null)
					continue;
//				cell.setCellType(CellType.STRING);
//				String value = cell.getStringCellValue();
				String value = getStringCellValue(cell);
				if (value.trim().length() == 0)
					continue;
				map.put(titles[j], value);
			}
			if (map.size() == 0)
				continue;
			list.add(map);
		}
		return list;
	}

	private static String getStringCellValue(Cell cell) {
		CellType cellType = cell.getCellType();
		if (cellType == CellType.NUMERIC) {
			return String.valueOf(cell.getNumericCellValue());
		} else if (cellType == CellType.FORMULA) {
			return cell.getCellFormula();
		} else {
			return cell.getStringCellValue();
		}
	}
	
}